﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net.Http.Headers;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using Com.JunCaiSoft.Accountant.Common;
using Com.JunCaiSoft.AutoMapper;
using Com.JunCaiSoft.Common.AssistClass;
using Com.JunCaiSoft.Common.Controller;
using Com.JunCaiSoft.Common.Enums;
using Com.JunCaiSoft.ConstructionCashFlow.Common.Enum;
using Com.JunCaiSoft.ConstructionCashFlow.Entity;
using Com.JunCaiSoft.ConstructionCashFlow.IDao;
using Com.JunCaiSoft.ConstructionCashFlow.Model;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using Newtonsoft.Json;

namespace Com.JunCaiSoft.API.ConstructionCashFlow.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    [Authorize]
    public class CashFlowController : BaseController<CashFlowView,CashFlowItem,CashFlow,Guid>
    {
        private IConfiguration configuration { get; set; }
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="cashflowdao"></param>
        /// <param name="Configuration"></param>
        public CashFlowController(ICashFlowDao cashflowdao, IConfiguration Configuration) : base(cashflowdao)
        {
            configuration = Configuration;
        }
        /// <summary>
        /// 按用户ID批量删除
        /// </summary>
        /// <param name="DeleteList"></param>
        /// <returns></returns>
        [HttpPatch]
        public virtual async Task<ActionResult> DeleteList(IEnumerable<Guid> DeleteList)
        {
            try
            {
                await DaoAsync.dbContext.Database.BeginTransactionAsync();

                int result = await DaoAsync.DeleteListAsync<CashFlow>(x => DeleteList.Contains(x.Id));

                await DaoAsync.dbContext.Database.CommitTransactionAsync();

                return await Task.Run(() => Content(JsonConvert.SerializeObject(result)));

            }
            catch (Exception e)
            {
                await DaoAsync.dbContext.Database.RollbackTransactionAsync();
                throw e;
            }
            finally
            {
                await DaoAsync.dbContext.Database.CommitTransactionAsync();
            }
        }
        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="keyword"></param>
        /// <returns></returns>
        [HttpGet]
        public virtual async Task<ActionResult> ListByPageAsync(int pageIndex, int pageSize,String keyword)
        {
            try
            {
                String advanceStr = Request.Query["advance"].ToString();

                CashFlowQuery advanceQuery = JsonConvert.DeserializeObject<CashFlowQuery>(advanceStr);

                var columns = @" a.Id,
	                             a.Num,
	                             a.NumCode,
	                             c.Name + '/' + d.Name + '/' + e.Name ProjectAddress,
	                             pt.Name Company,
	                             b.Name BankName,
	                             b.Account BankAccount,
	                             SUBSTRING ( b.Account, LEN( b.Account ) - 3, 4 ) BankAccountTail,
	                             p.NumCode ProjectNumCode,
	                             p.Name ProjectName,
	                             a.Summary,
	                             a.BankForward MoneyForward,
	                             h.Name FundsType,
                                 a.FundsStatus,
                                 u.RealName PayOperator,
	                             a.Amount,
	                             a.AllocationTime,
	                             a.CreateTime ";

                var tables = @" CashFlow a
	                            LEFT JOIN Bank b ON a.BankId = b.Id
	                            LEFT JOIN Project p ON a.ProjectId = p.Id
	                            LEFT JOIN Administrative c ON p.Province = c.Code
	                            LEFT JOIN Administrative d ON p.City = d.Code
	                            LEFT JOIN Administrative e ON p.Zone = e.Code
	                            LEFT JOIN Partment pt ON p.Partment = pt.Id
	                            LEFT JOIN FundsType h ON a.FundsType = h.Code 
                                LEFT JOIN [User] u ON a.PayOperatorId = u.Id";

                var where = @" 1 = 1 ";

                List<SqlParameter> cashParams = new List<SqlParameter>() { };

                

                if ((keyword != "" && keyword != null) || (advanceQuery != null && advanceQuery.BankOrProject != "" && advanceQuery.BankOrProject != null))
                {
                    String QueryBankOrProjectStr = "";

                    if(advanceQuery != null && !String.IsNullOrEmpty(advanceQuery.BankOrProject))
                    {
                        QueryBankOrProjectStr = advanceQuery.BankOrProject;
                    }
                    else if(!String.IsNullOrEmpty(keyword))
                    {
                        QueryBankOrProjectStr = keyword;
                    }

                    Regex rex = new Regex(@"^\d{4}$");

                    if (QueryBankOrProjectStr.Length == 4 && rex.IsMatch(QueryBankOrProjectStr))
                    {
                        where += " AND SUBSTRING ( b.Account, LEN( b.Account ) - 3, 4 ) = @BankAccountTail ";

                        SqlParameter QueryBankAccountTail = new SqlParameter("@BankAccountTail", QueryBankOrProjectStr);

                        cashParams.Add(QueryBankAccountTail);
                    }
                    else
                    {
                        where += " and ( b.Name like @BankOrProject or p.Name like @BankOrProject) ";

                        SqlParameter QueryBankOrProject = new SqlParameter("@BankOrProject", "%" + QueryBankOrProjectStr + "%");

                        cashParams.Add(QueryBankOrProject);
                    }
                }

                if (advanceQuery != null)
                {
                    if (!String.IsNullOrEmpty(advanceQuery.BookMark))
                    {
                        where += " and ( a.BankForward = @BookMark) ";

                        SqlParameter BookMarkParam = new SqlParameter("@BookMark", Enum.Parse(typeof(BookMark), advanceQuery.BookMark));

                        cashParams.Add(BookMarkParam);
                    }

                    if (!String.IsNullOrEmpty(advanceQuery.FundsStatus))
                    {
                        where += " and ( a.FundsStatus = @FundsStatus) ";

                        SqlParameter FundsStatusParam = new SqlParameter("@FundsStatus", advanceQuery.FundsStatus);

                        cashParams.Add(FundsStatusParam);
                    }

                    if (!String.IsNullOrEmpty(advanceQuery.FundsType))
                    {
                        where += " and ( a.FundsType = @FundsType) ";

                        SqlParameter FundsTypeParam = new SqlParameter("@FundsType", advanceQuery.FundsType);

                        cashParams.Add(FundsTypeParam);
                    }

                    if (Guid.Empty != advanceQuery.Operator)
                    {
                        where += " and ( a.PayOperatorId = @PayOperator) ";

                        SqlParameter PayOperatorParam = new SqlParameter("@PayOperator", advanceQuery.Operator);

                        cashParams.Add(PayOperatorParam);
                    }

                    if (!String.IsNullOrEmpty(advanceQuery.MinMoney))
                    {
                        where += " and ( a.Amount >= @MinMoney) ";

                        SqlParameter MinMoneyParam = new SqlParameter("@MinMoney", Double.Parse(advanceQuery.MinMoney));

                        cashParams.Add(MinMoneyParam);
                    }

                    if (!String.IsNullOrEmpty(advanceQuery.MaxMoney))
                    {
                        where += " and ( a.Amount <= @MaxMoney) ";

                        SqlParameter MaxMoneyParam = new SqlParameter("@MaxMoney", Double.Parse(advanceQuery.MaxMoney));

                        cashParams.Add(MaxMoneyParam);
                    }

                    if (advanceQuery.DateRange != null && advanceQuery.DateRange.Count != 0)
                    {
                        if(!String.IsNullOrEmpty(advanceQuery.DateRange[0]))
                        {
                            where += " and ( a.AllocationTime >= @StarDate ) ";

                            SqlParameter DateRangeStartParam = new SqlParameter("@StarDate", DateTime.Parse(advanceQuery.DateRange[0]).ToString("yyyyMMdd"));

                            cashParams.Add(DateRangeStartParam);
                        }

                        if (!String.IsNullOrEmpty(advanceQuery.DateRange[1]))
                        {
                            where += " and ( a.AllocationTime <= @EndDate ) ";

                            SqlParameter DateRangeEndParam = new SqlParameter("@EndDate", DateTime.Parse(advanceQuery.DateRange[1]).ToString("yyyyMMdd"));

                            cashParams.Add(DateRangeEndParam);
                        }
                    }
                }

                var resultList = await DaoAsync.QuerySQLByPageQuickAsync<CashFlowItem>(pageIndex, pageSize, " a.AllocationTime desc ", columns, tables, where, cashParams.Select(x => ((ICloneable)x).Clone()).ToArray());

                return await Task.Run(() => Content(JsonConvert.SerializeObject(resultList)));
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        [HttpGet]
        public override async Task<ActionResult> GetAsync(Guid Id)
        {
            var columns = @" a.Id,
	                         a.Num,
	                         a.NumCode,
	                         a.BankId BankGuid,
	                         a.ProjectId ProjectGuid,
                             a.SupplierFactory,
                             a.SupplierBankAccount,
	                         a.Summary,
	                         Cast(a.BankForward as nvarchar) MoneyForward,
	                         a.FundsType,
                             a.Amount,
                             a.FundsStatus,
	                         a.AllocationTime,
                             a.FreezeMoney,
                             a.PayOperatorId,
	                         a.CreateTime ";

            var tables = @" CashFlow a ";

            var where = @" a.Id = @Id ";

            SqlParameter parameter = new SqlParameter("@Id", Id);

            List<SqlParameter> arrary = new List<SqlParameter>();

            arrary.Add(parameter);

            var resultList = await DaoAsync.QuerySQLByPageQuickAsync<CashFlowView>(1, 10, " a.CreateTime desc ", columns, tables, where, arrary.Select(x => ((ICloneable)x).Clone()).ToArray());

            var resultOne = resultList.items != null ? resultList.items.FirstOrDefault() : new CashFlowView();

            return await Task.Run(() => Content(JsonConvert.SerializeObject(resultOne)));
        }
        /// <summary>
        /// 获取业务流编码
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public virtual async Task<ActionResult> CreateCashFlowCode()
        {
            try
            {

                int result = 0;

                return await Task.Run(() => Content(JsonConvert.SerializeObject(result)));
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        [HttpPost]
        public virtual async Task<ActionResult> UploadImportFile(IFormFile file)
        {
            try
            {
                if (file == null)
                {
                    return BadRequest();
                }

                var filename = ContentDispositionHeaderValue.Parse(file.ContentDisposition).FileName; // 原文件名（包括路径）

                var extName = filename.Substring(filename.LastIndexOf('.')).Replace("\"", "");// 扩展名

                string shortfilename = $"{Guid.NewGuid()}{extName}";// 新文件名

                string fileSavePath = Directory.GetCurrentDirectory() + @"/Upload/";//文件临时目录，导入完成后 删除

                filename = fileSavePath + shortfilename; // 新文件名（包括路径）

                if (!Directory.Exists(fileSavePath))
                {
                    Directory.CreateDirectory(fileSavePath);
                }

                using (FileStream fs = System.IO.File.Create(filename)) // 创建新文件
                {
                    file.CopyTo(fs);// 复制文件

                    fs.Flush();// 清空缓冲区数据

                    //根据 filename 【文件服务器磁盘路径】可对文件进行业务操作
                }

                var result = new UploadResult {
                    Importfile = shortfilename
                };

                return await Task.Run(() => Content(JsonConvert.SerializeObject(result)));
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        [HttpGet]
        public virtual async Task<ActionResult> ImportData(String Importfile,String BankName, Guid BankGuid, ImportType ImportWay,Guid Payer)
        {
            try
            {
                await DaoAsync.dbContext.Database.BeginTransactionAsync();

                if (String.IsNullOrEmpty(Importfile))
                {
                    return await Task.Run(() => Content(JsonConvert.SerializeObject("ImportFileNotFound")));
                }

                if (BankGuid == Guid.Empty && ImportWay == ImportType.FUNDS)
                {
                    return await Task.Run(() => Content(JsonConvert.SerializeObject("BankNotFound")));
                }

                string fileSavePath = Directory.GetCurrentDirectory() + @"/Upload/";//文件临时目录，导入完成后 删除

                string filename = fileSavePath + Importfile;

                var ImportDataTable = NPOIExtension.ImportExceltoDt(filename);

                List<CashFlow> importCashFlow = new List<CashFlow>();

                List<CFImportModel> failList = new List<CFImportModel>();

                var NumCode = TimeStamp.timestamp;

                for (int i = 0; i < ImportDataTable.Rows.Count; i++)
                {
                    try
                    {
                        CashFlow cashFlow = new CashFlow();
                        cashFlow.Id = Guid.NewGuid();
                        cashFlow.Num = 0;
                        cashFlow.NumCode = (++NumCode).ToString();

                        var PayBank = (await DaoAsync.ListAsync<Bank>(x => x.Account == ImportDataTable.Rows[i][0].ToString())).FirstOrDefault();
                        if (PayBank == null)
                        {
                            failList.Add(new CFImportModel
                            {
                                BankAccount = ImportDataTable.Rows[i][0].ToString(),
                                ProjectName = ImportDataTable.Rows[i][1].ToString(),
                                SupplierFactory = ImportDataTable.Rows[i][2].ToString(),
                                MoneyForward = ImportDataTable.Rows[i][3].ToString(),
                                FundsType = ImportDataTable.Rows[i][4].ToString(),
                                Amount = Double.Parse(String.IsNullOrEmpty(ImportDataTable.Rows[i][5].ToString()) ? "0.0" : ImportDataTable.Rows[i][5].ToString()),
                                Summary = ImportDataTable.Rows[i][6].ToString(),
                                AllocationTime = Int32.Parse(ImportDataTable.Rows[i][7].ToString()),

                                FailReason = "银行账户无法找到！"
                            });
                            continue;
                        }
                        cashFlow.BankId = PayBank.Id;
                        ImportDataTable.Rows[i][1].ToString();

                        var ImportProjectName = ImportDataTable.Rows[i][1].ToString();
                        var AboutProject = (await DaoAsync.ListAsync<Project>(x => x.Name == ImportProjectName)).FirstOrDefault();
                        if (ImportProjectName != null && ImportProjectName != "" && AboutProject == null)
                        {
                            failList.Add(new CFImportModel
                            {
                                BankAccount = ImportDataTable.Rows[i][0].ToString(),
                                ProjectName = ImportDataTable.Rows[i][1].ToString(),
                                SupplierFactory = ImportDataTable.Rows[i][2].ToString(),
                                MoneyForward = ImportDataTable.Rows[i][3].ToString(),
                                FundsType = ImportDataTable.Rows[i][4].ToString(),
                                Amount = Double.Parse(String.IsNullOrEmpty(ImportDataTable.Rows[i][5].ToString()) ? "0.0" : ImportDataTable.Rows[i][5].ToString()),
                                Summary = ImportDataTable.Rows[i][6].ToString(),
                                AllocationTime = Int32.Parse(ImportDataTable.Rows[i][7].ToString()),

                                FailReason = "项目名称无法找到！"
                            });
                            continue;
                        }
                        cashFlow.ProjectId = AboutProject != null ? AboutProject.Id : Guid.Empty;

                        cashFlow.BankForward = ImportDataTable.Rows[i][3].ToString() == "收入" ? BookMark.Credit : BookMark.Debit;
                        cashFlow.ProjectForward = ImportDataTable.Rows[i][3].ToString() == "收入" ? BookMark.Debit : BookMark.Credit;

                        var FundsType = (await DaoAsync.ListAsync<FundsType>(x => x.Name == ImportDataTable.Rows[i][4].ToString())).FirstOrDefault();
                        if (FundsType == null)
                        {
                            await DaoAsync.InsertAsync<FundsType>
                            (
                                new FundsType()
                                {
                                    Code = TimeStamp.timestamp.ToString(),
                                    Name = ImportDataTable.Rows[i][4].ToString()
                                }
                            ); ;
                        }
                        FundsType = (await DaoAsync.ListAsync<FundsType>(x => x.Name == ImportDataTable.Rows[i][4].ToString())).FirstOrDefault();
                        cashFlow.FundsType = FundsType.Code;

                        cashFlow.Amount = Double.Parse(ImportDataTable.Rows[i][5].ToString());

                        cashFlow.Summary = ImportDataTable.Rows[i][6].ToString();
                        cashFlow.AllocationTime = Int32.Parse(ImportDataTable.Rows[i][7].ToString());

                        cashFlow.FundsStatus = 1;
                        cashFlow.SupplierFactory = ImportDataTable.Rows[i][2].ToString();
                        cashFlow.PayOperatorId = Payer;

                        importCashFlow.Add(cashFlow);
                    }
                    catch (Exception e)
                    {
                        throw e;
                    }
                }

                SqlConnection connection = new SqlConnection(configuration.GetConnectionString("MSSQLConnection"));

                SqlBulkCopyExtension.BulkCopy<CashFlow>(connection, importCashFlow, importCashFlow.Count, null, 120);

                //处理完成后，删除上传的文件
                if (System.IO.File.Exists(filename))
                {
                    System.IO.File.Delete(filename);
                }

                await DaoAsync.dbContext.Database.CommitTransactionAsync();

                return await Task.Run(() => Content(JsonConvert.SerializeObject(failList)));
            }
            catch(Exception e)
            {
                await DaoAsync.dbContext.Database.RollbackTransactionAsync();

                throw e;
            }
        }
        [HttpGet]
        [AllowAnonymous]
        public virtual FileResult DownloadExportFile(ImportType importType)
        {
            try
            {
                string DownloadFundsFile = Directory.GetCurrentDirectory() + @"/ImportTemplate/ProjectApplication.xlsx";

                string DownloadCashFlowFile = Directory.GetCurrentDirectory() + @"/ImportTemplate/CashFlowSheetModel.xlsx";

                String DownloadFundsName = "项目资金支付申请表.xlsx";

                String DownloadCashFlowName = "资金流水明细导入模板.xlsx";

                var DownloadFile = importType == ImportType.FUNDS ? DownloadFundsFile : DownloadCashFlowFile;

                var DownloadName = importType == ImportType.FUNDS ? DownloadFundsName : DownloadCashFlowName;

                return File(System.IO.File.ReadAllBytes(DownloadFile), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",DownloadName);
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        [HttpDelete]
        public virtual async Task<ActionResult> DeleteUploadFile(String UploadFile)
        {
            try
            {
                string fileSavePath = Directory.GetCurrentDirectory() + @"\Upload\";//文件临时目录，导入完成后 删除

                string filename = fileSavePath + UploadFile;

                //处理完成后，删除上传的文件
                if (System.IO.File.Exists(filename))
                {
                    System.IO.File.Delete(filename);
                }

                return await Task.Run(() => Content(JsonConvert.SerializeObject(true)));
            }
            catch(Exception e)
            {
                return await Task.Run(() => Content(JsonConvert.SerializeObject(false)));
            }
            
        }
    }
}
